<?php
require_once "ra_global.php";

file_put_contents('log.txt', 'abc');

$nowtime=date('Y-m-01', strtotime(date("Y-m-d")));//当月开始时间 Y-m-d格式 1月：1451577600  2月：1454256000  3月：1456761600
$currentMonth = date('m', strtotime($nowtime));
$currentYear = date('Y', strtotime($nowtime));

// 上个月的起始和终止时间
$prevstime=strtotime("$nowtime -1 month");
$prevetime=strtotime($nowtime);

// 上个月的前三个月的起始和终止时间
$monthsstime=strtotime("$nowtime -4 month");
$monthsetime=strtotime("$nowtime -1 month");

// 计算本季度增长率
// 先根据当前月份得出上个季度的起始月终止月，以及上上个季度的起始月终止月，算出季度增长率
$lastSeasonEndMonth = getLastSeasonEndMonth($nowtime);
$lastSeasonStartMonth=strtotime(date('Y-m-01', $lastSeasonEndMonth)."-2 month");

$lastLastSeasonStartMonth = strtotime(date('Y-m-01', $lastSeasonEndMonth)."-5 month");
$lastLastSeasonEndMonth = strtotime(date('Y-m-01', $lastSeasonEndMonth)."-2 month");

$success = true;
$db->ig2_query('BEGIN');

// 查询每个商户的上个月的销量，和上个月的前三个月的销量，计算本月增长率
// 查询每个商户的上个季度的销量，和上上个季度的销量，计算季度增长率
$querySql = "SELECT s.id as shopid";
$querySql .= ", (SELECT count(1) FROM mae_sell ss INNER JOIN mae_user u ON ss.uid = u.id INNER JOIN mae_role r ON u.id = r.uid WHERE r.shop_id = s.id AND ss.create_time >= ".$prevstime." AND ss.create_time <= ".$prevetime.") as prevmonth";
$querySql .= ", (SELECT count(1) FROM mae_sell ss INNER JOIN mae_user u ON ss.uid = u.id INNER JOIN mae_role r ON u.id = r.uid WHERE r.shop_id = s.id AND ss.create_time >= ".$monthsstime." AND ss.create_time <= ".$monthsetime.") as avr3month";
$querySql .= ", (SELECT count(1) FROM mae_sell ss INNER JOIN mae_user u ON ss.uid = u.id INNER JOIN mae_role r ON u.id = r.uid WHERE r.shop_id = s.id AND ss.create_time >= ".$lastSeasonStartMonth." AND ss.create_time <= ".$lastSeasonEndMonth.") as lastseason";
$querySql .= ", (SELECT count(1) FROM mae_sell ss INNER JOIN mae_user u ON ss.uid = u.id INNER JOIN mae_role r ON u.id = r.uid WHERE r.shop_id = s.id AND ss.create_time >= ".$lastLastSeasonStartMonth." AND ss.create_time <= ".$lastLastSeasonEndMonth.") as lastlastseason";
$querySql .= " FROM mae_shop s";

$shops = $db->ig2_query_select($querySql);
foreach($shops as $shop) {
    if (!$success) {
        break;
    }
    $avg = 0;
    if ($shop['avr3month'] != 0) {
        $avg = round($shop['avr3month'] / 3);
    }

    $growthRate = 0;
    if ($avg != 0) {
        $growthRate = round(($shop['prevmonth'] - $avg) * 100 / $avg);
    }

    $qcurrent = 0;
    if ($shop['lastlastseason'] != 0) {
        $qcurrent = round(($shop['lastseason'] - $shop['lastlastseason']) * 100 / $shop['lastlastseason']);
    }

    // 计算当月奖励销售指标和奖励积分
    $purpose = 0;
    $addscore = 0;
    if ($avg < 30 && $avg > 0) {
        $purpose = round($avg * 1.2);
        $addscore = round($avg * 0.2 * 15 / 2 * 5);
    } else if ($avg < 70 && $avg >= 30) {
        $purpose = round($avg * 1.07);
        $addscore = round($avg * 0.07 * 15 / 2 * 5);
    } else if ($avg < 100 && $avg >= 70) {
        $purpose = round($avg * 1.05);
        $addscore = round($avg * 0.05 * 15 / 2 * 5);
    } else if ($avg >= 100) {
        $purpose = round($avg * 1.03);
        $addscore = round($avg * 0.03 * 15 / 2 * 5);
    }

    $exist = $db->ig2_want('mae_growth_rate','shopid='.$shop['shopid'].' AND statyear="'.$currentYear.'"');
    // 如果没有记录，新插入一条，否则更新对应字段
    if (!$exist) {
        $insertSql = "INSERT INTO mae_growth_rate(shopid, ".getMonthField($currentMonth).", monthcurrent, avg3month, ".getSeasonField($currentMonth).", qcurrent, statyear, purpose, addscore) VALUES (".$shop['shopid'].", ".$growthRate.", ".$growthRate.", ".$avg.", ".$qcurrent.", ".$qcurrent.", '".$currentYear."', ".$purpose.", ".$addscore.")";
        $success = $db->ig2_query($insertSql);
    } else {
        $updateSql = "UPDATE mae_growth_rate SET ".getMonthField($currentMonth)." = ".$growthRate.", monthcurrent = ".$growthRate.", avg3month = ".$avg.", ".getSeasonField($currentMonth)." = ".$qcurrent.", qcurrent = ".$qcurrent.", purpose = ".$purpose.", addscore = ".$addscore." WHERE shopid = ".$shop['shopid']." AND statyear = '".$currentYear."'";
        $success = $db->ig2_query($updateSql);
    }
}
if ($success) {
    $db->ig2_query('COMMIT');
} else {
    $db->ig2_query('ROLLBACK');
}

echo $success;

function getMonthField($currentMonth) {
    $monthField = "";
    if ($currentMonth == "01") {
        $monthField = "month1";
    } else if ($currentMonth == "02") {
        $monthField = "month2";
    } else if ($currentMonth == "03") {
        $monthField = "month3";
    } else if ($currentMonth == "04") {
        $monthField = "month4";
    } else if ($currentMonth == "05") {
        $monthField = "month5";
    } else if ($currentMonth == "06") {
        $monthField = "month6";
    } else if ($currentMonth == "02") {
        $monthField = "month2";
    } else if ($currentMonth == "07") {
        $monthField = "month7";
    } else if ($currentMonth == "08") {
        $monthField = "month8";
    } else if ($currentMonth == "09") {
        $monthField = "month9";
    } else if ($currentMonth == "10") {
        $monthField = "month10";
    } else if ($currentMonth == "11") {
        $monthField = "month11";
    } else if ($currentMonth == "12") {
        $monthField = "month12";
    }

    return $monthField;
}

function getSeasonField($currentMonth) {
    $seasonField = "";
    if ($currentMonth == "01" || $currentMonth == "02" || $currentMonth == "03") {
        $seasonField = "q1";
    } else if ($currentMonth == "04" || $currentMonth == "05" || $currentMonth == "06") {
        $seasonField = "q2";
    } else if ($currentMonth == "07" || $currentMonth == "08" || $currentMonth == "09") {
        $seasonField = "q3";
    } else if ($currentMonth == "10" || $currentMonth == "11" || $currentMonth == "12") {
        $seasonField = "q4";
    }

    return $seasonField;
}

function getLastSeasonEndMonth($nowtime) {
    $currentMonth = date('m', strtotime($nowtime));
    if ($currentMonth == "01") {
        return strtotime("$nowtime -1 month");
    } else if ($currentMonth == "02") {
        return strtotime("$nowtime -2 month");
    } else if ($currentMonth == "03") {
        return strtotime("$nowtime -3 month");
    } else if ($currentMonth == "04") {
        return strtotime("$nowtime -1 month");
    } else if ($currentMonth == "05") {
        return strtotime("$nowtime -2 month");
    } else if ($currentMonth == "06") {
        return strtotime("$nowtime -3 month");
    } else if ($currentMonth == "07") {
        return strtotime("$nowtime -1 month");
    } else if ($currentMonth == "08") {
        return strtotime("$nowtime -2 month");
    } else if ($currentMonth == "09") {
        return strtotime("$nowtime -3 month");
    } else if ($currentMonth == "10") {
        return strtotime("$nowtime -1 month");
    } else if ($currentMonth == "11") {
        return strtotime("$nowtime -2 month");
    } else if ($currentMonth == "12") {
        return strtotime("$nowtime -3 month");
    }
}
?>